import pandas as pd

# 读取Excel文件
excel_file = '武银大厦方案距离汇总.xlsx'  # Excel文件路径
output_excel = '武银大厦方案评价结果.xlsx'  # 输出的Excel文件路径
df = pd.read_excel(excel_file)

# 计算每个指标的最大值和最小值
N_max = df['方案数'].max()
N_min = df['方案数'].min()
D_max = df['最短距离'].max()
D_min = df['最短距离'].min()

# 权重设置
w_N = 0.6777  # 方案数权重
w_D = 0.3333  # 最短距离权重

# 标准化函数
def normalize(value, min_val, max_val):
    return (value - min_val) / (max_val - min_val)

# 计算标准化值和评价分数
df['Normalized_N'] = normalize(df['方案数'].values, N_min, N_max)
df['Normalized_D'] = 1 - normalize(df['最短距离'].values, D_min, D_max)  # 距离越短越好
df['Score'] = w_N * df['Normalized_N'] + w_D * df['Normalized_D']

# 将分数线性映射到[0, 100]区间
df['Final Score'] = 100 * (df['Score'] - df['Score'].min()) / (df['Score'].max() - df['Score'].min())

# 选择需要输出的列
result_df = df[['起点名称', '方案数', '最短距离','Final Score']]

# 保存到Excel文件
result_df.to_excel(output_excel, index=False)
print(f"评价结果已保存到 '{output_excel}'")